﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using IDAL;
using Models;

namespace DAL
{
    public class CareerInfoDAL:ICareerInfo
    {
        public const string SQL_GET_ALL_CAREERINFO = @"select * from CareerInfo order by PublishTime desc";

        public DataBase DB = new DataBase();

        public List<CareerInfo> GetAllCareerInfo()
        {
            List<CareerInfo> list = new List<CareerInfo>();
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(SQL_GET_ALL_CAREERINFO);
            while (reader.Read())
            {
                CareerInfo careerinfo = new CareerInfo();
                ReadToCareerInfo(reader, careerinfo);
                list.Add(careerinfo);
            }
            DB.CloseDB();
            return list;
        }

        public void ReadToCareerInfo(SqlDataReader reader, CareerInfo careerinfo)
        {
            try
            {
                careerinfo.CareerID = Int32.Parse(reader["CareerID"].ToString());
            }
            catch { }
            careerinfo.CareerCName = reader["CareerCName"] is DBNull ? "" : reader["CareerCName"].ToString();
            careerinfo.CareerEName = reader["CareerEName"] is DBNull ? "" : reader["CareerEName"].ToString();
            careerinfo.CareerContent = reader["CareerContent"] is DBNull ? "" : reader["CareerContent"].ToString();
            try
            {
                careerinfo.PublishTime = (DateTime)reader["PublishTime"];
            }
            catch { }
            careerinfo.PicURL = reader["PicURL"] is DBNull ? "" : reader["PicURL"].ToString();
        }

        public int AddCareerInfo(CareerInfo career)
        {
            int isSuccess = -1;
            string sql = @"INSERT INTO CareerInfo([CareerCName],[CareerEName],[CareerContent],[PublishTime],[PicURL]) 
            VALUES( '" + career.CareerCName + @"',
                    '" + career.CareerEName + @"',
                    '" + career.CareerContent + @"',
                    '" + career.PublishTime + @"',
                    '" + career.PicURL + @"');";
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }
        public CareerInfo GetOneCareer(int id)
        {
            CareerInfo career = new CareerInfo();
            string sql = "SELECT TOP 1 * FROM CareerInfo WHERE CareerID = " + id;
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(sql);
            while (reader.Read())
            {
                ReadToCareerInfo(reader, career);
            }
            DB.CloseDB();
            return career;
        }

        public int UpdateCareerInfo(CareerInfo career)
        {
            int isSuccess = -1;
            string sql = @"UPDATE CareerInfo SET 
                    [CareerCName] = '" + career.CareerCName + @"',
                    [CareerEName] = '" + career.CareerEName + @"',
                    [CareerContent] = '" + career.CareerContent + @"',
                    [PublishTime] = '" + career.PublishTime + @"',
                    [PicURL] = '" + career.PicURL + @"'
                    WHERE [CareerID] = " + career.CareerID;
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }

        public int DeleteCareerInfo(int cid)
        {
            int isSuccess = -1;
            string sql = "DELETE CareerInfo WHERE CareerID = " + cid;
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }
    }
}
